-- ==============================================================
-- SQL Server 版本 - 系统授权表
-- ==============================================================

-- 如果表存在则删除
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_authorization]') AND type in (N'U'))
DROP TABLE [dbo].[sys_authorization]
GO

-- 创建系统授权表
CREATE TABLE [dbo].[sys_authorization] (
    [auth_id] BIGINT IDENTITY(1,1) NOT NULL,
    [auth_code] NVARCHAR(500) NOT NULL,
    [auth_name] NVARCHAR(100) NULL,
    [machine_code] NVARCHAR(200) NULL,
    [start_time] DATETIME NULL,
    [expire_time] DATETIME NULL,
    [max_users] INT NULL,
    [status] NCHAR(1) NULL DEFAULT '0',
    [is_permanent] NCHAR(1) NULL DEFAULT '0',
    [authorized_by] NVARCHAR(100) NULL,
    [auth_desc] NVARCHAR(500) NULL,
    [create_by] NVARCHAR(64) NULL DEFAULT '',
    [create_time] DATETIME NULL,
    [update_by] NVARCHAR(64) NULL DEFAULT '',
    [update_time] DATETIME NULL,
    [remark] NVARCHAR(500) NULL,
    CONSTRAINT [PK_sys_authorization] PRIMARY KEY CLUSTERED ([auth_id] ASC)
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- 创建唯一索引
CREATE UNIQUE NONCLUSTERED INDEX [idx_auth_code] ON [dbo].[sys_authorization]
(
    [auth_code] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

-- 添加表注释
EXEC sys.sp_addextendedproperty 
    @name=N'MS_Description', 
    @value=N'系统授权表' , 
    @level0type=N'SCHEMA', @level0name=N'dbo', 
    @level1type=N'TABLE', @level1name=N'sys_authorization'
GO

-- 添加字段注释
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'授权ID' , @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_authorization', @level2type=N'COLUMN', @level2name=N'auth_id'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'授权码（加密存储）' , @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_authorization', @level2type=N'COLUMN', @level2name=N'auth_code'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'授权名称' , @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_authorization', @level2type=N'COLUMN', @level2name=N'auth_name'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'机器码' , @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_authorization', @level2type=N'COLUMN', @level2name=N'machine_code'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'授权开始时间' , @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_authorization', @level2type=N'COLUMN', @level2name=N'start_time'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'授权过期时间' , @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_authorization', @level2type=N'COLUMN', @level2name=N'expire_time'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最大用户数' , @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_authorization', @level2type=N'COLUMN', @level2name=N'max_users'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'状态（0正常 1停用）' , @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_authorization', @level2type=N'COLUMN', @level2name=N'status'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否永久授权（0否 1是）' , @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_authorization', @level2type=N'COLUMN', @level2name=N'is_permanent'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'授权人' , @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_authorization', @level2type=N'COLUMN', @level2name=N'authorized_by'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'授权说明' , @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_authorization', @level2type=N'COLUMN', @level2name=N'auth_desc'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建者' , @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_authorization', @level2type=N'COLUMN', @level2name=N'create_by'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间' , @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_authorization', @level2type=N'COLUMN', @level2name=N'create_time'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新者' , @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_authorization', @level2type=N'COLUMN', @level2name=N'update_by'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新时间' , @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_authorization', @level2type=N'COLUMN', @level2name=N'update_time'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'sys_authorization', @level2type=N'COLUMN', @level2name=N'remark'
GO

-- ==============================================================
-- 插入默认授权数据（测试用）
-- ==============================================================
SET IDENTITY_INSERT [dbo].[sys_authorization] ON
GO

INSERT INTO [dbo].[sys_authorization] 
    ([auth_id], [auth_code], [auth_name], [machine_code], [start_time], [expire_time], 
     [max_users], [status], [is_permanent], [authorized_by], [auth_desc], 
     [create_by], [create_time], [update_by], [update_time], [remark])
VALUES 
    (1, N'DEFAULT_AUTH_CODE_2025_PERMANENT', N'默认永久授权', NULL, GETDATE(), NULL, 
     100, N'0', N'1', N'system', N'系统默认永久授权', 
     N'admin', GETDATE(), NULL, NULL, N'系统初始授权')
GO

SET IDENTITY_INSERT [dbo].[sys_authorization] OFF
GO
